//
//  DataBase.m
//  MySqlite
//
//  Created by dllo on 15/7/10.
//  Copyright (c) 2015年 dllo. All rights reserved.
//

#import "DataBase.h"


static DataBase * dataBaseHandle = nil;

@implementation DataBase

+(DataBase *)shareDataBase
{
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        dataBaseHandle = [[DataBase alloc] init];
    });
    return dataBaseHandle;
}

#pragma mark- 开启数据库

//创建一个数据库对象
static sqlite3 * db;

-(void)openDB
{
    NSString * music = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
    
    NSString * musicPath = [music stringByAppendingString:@"/music.sqlite"];
    
    
    if (db != nil) {
        NSLog(@"数据库已经开启");
        return;
    }
    
    //打开数据库的方法 参数1：路径 参数2：数据库对象
    int result = sqlite3_open([musicPath UTF8String], &db);
    
    if (result == SQLITE_OK) {
        NSLog(@"数据库开启成功");
    }else{
        NSLog(@"数据库开启失败");
    }
}

#pragma mark- 创建数据库表格
-(void)createTable
{
    
    

  
    
    
    //创建建表的sql语句
    NSString * createSql = @"CREATE TABLE IF NOT EXISTS music(number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, singerName TEXT, url TEXT)";
    
    
    //执行sql语句
    int result = sqlite3_exec(db, [createSql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}

#pragma mark- 往数据库表格中插入学生信息
- (void)insertModelInfo:(DcFirstModel *)model
{
    
    
 
    //创建插入学生信息的sql语句
    NSString * insertSql = [NSString stringWithFormat:@"INSERT INTO music( name, singerName, url) VALUES('%@', '%@', '%@')",model.name, model.singerName, model.url];
    
    //执行sql语句
    int result = sqlite3_exec(db, [insertSql UTF8String], NULL, NULL, NULL);
    
    if (result != SQLITE_OK) {
        NSLog(@"插入信息失败");
    } 
}



#pragma mark- 删除一个学生信息
- (void)deleteModelInfo:(NSInteger)number
{
    //创建删除学生信息sql语句
    NSString * deleteSql = [NSString stringWithFormat:@"DELETE FROM music WHERE number = '%ld'",(long)number];
    
    //执行sql语句
    int result = sqlite3_exec(db, [deleteSql UTF8String], NULL, NULL, NULL);
    
    if (result != SQLITE_OK) {
        NSLog(@"删除失败");
    }
}

#pragma mark- 查询所有学生信息
- (NSMutableArray *)selectModel
{
    //创建查询sql语句
    NSString * selectSql = @"SELECT * FROM music";
    //创建数据库指针对象
    sqlite3_stmt * stmt = nil;
    
    //将数据库对象、SQL语句、指针对象关联在一起
    /*
     参数1：数据库对象
     参数2：查询语句
     参数3：查询语句字数限制（-1为不限制）
     参数4：数据库指针对象
     参数5：nil
     */
    int result = sqlite3_prepare_v2(db, [selectSql UTF8String], -1, &stmt, nil);
    
    //创建存放学生信息的大数组
    NSMutableArray * array = [[NSMutableArray alloc] init];
    
    if (result != SQLITE_OK) {
        NSLog(@"查询失败");
    }
    

    
    //循环遍历查询数据库表格每一行
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        //取出每一列数据
        int number = sqlite3_column_int(stmt, 0);
//        const unsigned char *song_name = sqlite3_column_text(stmt, 1);
//        const unsigned char *singer_name = sqlite3_column_text(stmt, 2);
        const unsigned char *name = sqlite3_column_text(stmt, 1);
        const unsigned char *singerName = sqlite3_column_text(stmt, 2);
        const unsigned char *url = sqlite3_column_text(stmt, 3);
        
        
//        NSString *song_nameString = [NSString stringWithUTF8String:(const char *)song_name];
//        NSString *singer_nameString = [NSString stringWithUTF8String:(const char *)singer_name];
        NSString *nameString = [NSString stringWithUTF8String:(const char *)name];
        NSString *singerNameString = [NSString stringWithUTF8String:(const char *)singerName];
        NSString *urlString = [NSString stringWithUTF8String:(const char *)url];
        
  
        DcFirstModel *model = [[DcFirstModel alloc] init];
        model.number = number;
//        model.song_name = song_nameString;
//        model.singer_name = singer_nameString;
        model.name = nameString;
        model.singerName = singerNameString;
        model.url = urlString;
        
        
        //将学生信息model类存入大数组
        [array addObject:model];
    }
    sqlite3_finalize(stmt);

    
    return array;
}

#pragma mark- 关闭数据库
-(void)closeDB
{
    
    int result = sqlite3_close(db);
    if (result != SQLITE_OK) {
        NSLog(@"关闭数据库失败");
    }
}



- (void)dropModelTable
{
    NSString *dropSql = @"DROP TABLE music";
    BOOL resutl = sqlite3_exec(db, dropSql.UTF8String, NULL, NULL, nil);
    if (resutl == SQLITE_OK) {
        NSLog(@"删除    成功");
    }
    else {
        NSLog(@"删除    失败");
    }
}






@end
